<?php
  class SQLQuery{
  
    private $dbhost;     
    private $dbuser;
    private $dbpass; 
    private $conn;
    
    function __construct($dbhost=null, $dbuser=null, $dbpass=null){
      $this->dbhost = $dbhost;
      $this->dbuser = $dbuser;      
      $this->dbpass = $dbpass;  
      $this->conn   = @mysql_connect($this->dbhost, $this->dbuser, $this->dbpass);        
    }
    
    public function readConnectDB(){  
      $this->conn =  mysql_connect($this->dbhost, $this->dbuser, $this->dbpass);
      if (!$this->conn){
        die('Could not connect: ' . mysql_error()); exit;
      }      
    }  
    
    public function deleteConnectDB(){       
      mysql_close($this->conn);      
      unset($this->conn);        
    }
    
    public function readAllDatabase(){
      $query = "SELECT DISTINCT(schemata.schema_name) as dbname
                FROM information_schema.columns, information_schema.schemata
                WHERE columns.table_schema = schemata.schema_name 
                AND columns.table_name = 'zk_ishopstore_item'               
              ";
              
      $stmt = mysql_query($query, $this->conn);            
      $i=0;
      while($row = mysql_fetch_object($stmt)){
        $objResult->dbname[$i]=$row->dbname;
        $i++;
      }
      
      //print_r($objResult);
      //$rowsAffected = mysql_num_rows($stmt); 
      //echo $rowsAffected;
      //mysql_close($con);      
      //unset($con);
         
      return $objResult;              
      
    }
    
  
    
    
    public function query($query){


      echo "################ ". substr($query, 0, 100)." ################<br><br>";
      
      $objAllDatabase = $this->readAllDatabase();
      foreach($objAllDatabase->dbname as $dbname){
        $select = mysql_select_db($dbname, $this->conn);
        if (!$select) {
            die ('Table unavailable : ' . mysql_error());
        }else{     
          $row = mysql_query($query, $this->conn);
          if($row){            
            echo "Query is affected ".$row." rows on <b>".$dbname."</b><br>";
          }else{
            echo "Query is affected 0 rows on <b>".$dbname."</b><br>";          
          }
        } 
      } 
      
      echo "################ End upgrade state. ################<br><br>";
 
    }
 
  }


  //Main function
  $dbhost = "okalimart.com";   
  $dbuser = "okalimar";
  $dbpass = "7mf8ez48PY"; 
  $db = new SQLQuery($dbhost, $dbuser, $dbpass);
  
  
  //###########################################
  // Desc : Add new itm_status field
  // Create : 2012-01-05
  // Update By : Foppy  
  //###########################################
  $query = "ALTER TABLE zk_ishopstore_item ADD COLUMN itm_status INT(11) NULL AFTER itm_has_file";  
  $db->query($query);
  
  
  //###########################################
  // Desc : Add new ctg_prefix field
  // Create : 2012-01-07
  // Update By : Foppy  
  //###########################################  
  $query = "ALTER TABLE zk_ishopstore_categories ADD COLUMN ctg_prefix VARCHAR(5) NULL AFTER ctg_name";    
  $db->query($query); 
  
  
  //###########################################
  // Desc : Add new ctg_prefix field
  // Create : 2012-01-14
  // Update By : Foppy  
  //###########################################  
  $query = "ALTER TABLE `zk_ishopstore_item`     
            ADD COLUMN `itm_subcategories_id` INT(11) NULL AFTER `itm_categories_id`,
            ADD COLUMN `itm_sale_price` DOUBLE DEFAULT '0' NULL AFTER `itm_price`,     
            ADD COLUMN `itm_amount` INT(50) NULL AFTER `itm_sale_price`,     
            ADD COLUMN `itm_shop_status` INT(11) NULL AFTER `itm_has_file`,    
            CHANGE `itm_price` `itm_price` DOUBLE DEFAULT '0' NULL ,     
            CHANGE `itm_status` `itm_recommend` INT(11) NULL ";
  $db->query($query);   


  //###########################################
  // Desc : Add new ctg_prefix field
  // Create : 2012-01-16
  // Update By : Foppy  
  //###########################################  
  $query = "CREATE TABLE `zk_ishopstore_subcategories` (
              `sct_id` INT(11) NOT NULL AUTO_INCREMENT,
              `sct_categories_id` INT(11) NOT NULL,
              `sct_name` TEXT,
              `sct_prefix` VARCHAR(5) DEFAULT NULL,
              PRIMARY KEY  (`sct_id`)
            ) ENGINE=MYISAM DEFAULT CHARSET=utf8";
  $db->query($query);   


  //###########################################
  // Desc : Add new img_user_id column
  // Create : 2012-01-16
  // Update By : Foppy  
  //###########################################  
  $query = "ALTER TABLE `zk_ishopstore_images`     
            ADD COLUMN `img_user_id` INT(11) NULL AFTER `img_id`";
  $db->query($query);   



  //###########################################
  // Desc : Create table zk_ishopstore_order 
  // Create : 2012-01-18
  // Update By : Unzo  
  //###########################################  
  $query = "CREATE TABLE `zk_ishopstore_order` (
            `order_id` INT(11) NOT NULL AUTO_INCREMENT,
            `order_orderer` VARCHAR(255),
            `order_phone_number` VARCHAR(15),
            `order_email` VARCHAR(255),
            `order_house_no` VARCHAR(10),
            `order_moo` VARCHAR(10),
            `order_soi` VARCHAR(10),
            `order_road` VARCHAR(255),
            `order_village` VARCHAR(255),
            `order_tambon` VARCHAR(255),
            `order_district` VARCHAR(255),
            `order_province` VARCHAR(255),
            `order_zipcode` VARCHAR(5),
            PRIMARY KEY  (`order_id`)
          ) ENGINE=MYISAM DEFAULT CHARSET=utf8";
  $db->query($query); 
 
  //###########################################
  // Desc : Create table zk_ishopstore_order 
  // Create : 2012-01-18
  // Update By : Unzo  
  //###########################################  
  $query = "CREATE TABLE `zk_ishopstore_order_item` (
              `orditm_id` INT(11) NOT NULL AUTO_INCREMENT,
              `orditm_item_id` INT(11) NOT NULL,
              `orditm_order_id` INT(11) NOT NULL,
              `orditm_quantity` INT(11) NOT NULL,
              PRIMARY KEY  (`orditm_id`)
            ) ENGINE=MYISAM DEFAULT CHARSET=utf8";
  $db->query($query); 
   
  
  //###########################################
  // Desc : Add new itm_subcategories_id column
  // Create : 2012-01-21
  // Update By : Foppy  
  //###########################################  
  $query = "ALTER TABLE `zk_ishopstore_item` ADD COLUMN `itm_subcategories_id` INT(11) NULL AFTER `itm_categories_id`";
  $db->query($query); 
  
  
  //###########################################
  // Desc : Drop column sct_prefix 
  // Create : 2012-01-23
  // Update By : Foppy  
  //###########################################  
  $query = "ALTER TABLE `zk_ishopstore_subcategories` DROP COLUMN `sct_prefix`";
  $db->query($query); 


  //###########################################
  // Desc : Create table zk_ishopstore_payment 
  // Create : 2012-01-23
  // Update By : Unzo  
  //########################################### 
  $query = "CREATE TABLE `zk_ishopstore_payment` (
            `pay_id` int(11) NOT NULL auto_increment,
            `pay_payment` varchar(255) default NULL,
            `pay_payment_bank_name` varchar(255) default NULL,
            `pay_payment_bank_branch` varchar(255) default NULL,
            `pay_payment_bank_account_name` varchar(255) default NULL,
            `pay_payment_bank_account_type` varchar(255) default NULL,
            `pay_payment_bank_account_no` varchar(20) default NULL,
            PRIMARY KEY  (`pay_id`)
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
  $db->query($query); 



  //###########################################
  // Desc : Create table zk_ishopstore_paid 
  // Create : 2012-01-23
  // Update By : Unzo  
  //###########################################  
  $query = "CREATE TABLE `zk_ishopstore_paid` (
            `pai_id` int(11) NOT NULL auto_increment,
            `pai_order_id` int(11) default NULL,
            `pai_payment_id` int(11) default NULL,
            `pai_payer_name` varchar(255) default NULL,
            `pai_paid` double default '0',
            `pai_transfer_date` datetime default NULL,
            `pai_transfer_inform_date` datetime default NULL,
            `pai_detail` text,
            `pai_status` int(5) default NULL,
            PRIMARY KEY  (`pai_id`)
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
  $db->query($query); 



  //###########################################
  // Desc : Create table zk_ishopstore_shipment 
  // Create : 2012-01-23
  // Update By : Unzo  
  //###########################################  
  $query = "CREATE TABLE `zk_ishopstore_shipment` (
              `shi_id` int(11) NOT NULL auto_increment,
              `shi_shipment` varchar(255) default NULL,
              `shi_shipment_detail` text,
              PRIMARY KEY  (`shi_id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
  $db->query($query);




  //###########################################
  // Desc : Add new order_status column
  // Create : 2012-01-23
  // Update By : Unzo  
  //###########################################  
  $query = "ALTER TABLE `zk_ishopstore_order` ADD COLUMN `order_status` VARCHAR(5) DEFAULT '1' NULL AFTER `order_zipcode`";
  $db->query($query);   




  //###########################################
  // Desc : Add new order_shipment_id column
  // Create : 2012-01-23
  // Update By : Unzo  
  //###########################################  
  $query = "ALTER TABLE `zk_ishopstore_order` ADD COLUMN `order_shipment_id` INT(11) NULL AFTER `order_status`";
  $db->query($query);     




  //###########################################
  // Desc : Create table zk_ishopstore_about 
  // Create : 2012-01-25
  // Update By : Foppy  
  //###########################################  
  $query = "CREATE TABLE `zk_ishopstore_about` (
              `abo_id` int(11) NOT NULL,
              `abo_how_to` text,
              `abo_about_us` text,
              `abo_contact_us` text,
              `abo_map_lat` text,
              `abo_map_lon` text
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
  $db->query($query);




  //###########################################
  // Desc : Add new  abo_map column
  // Create : 2012-01-25
  // Update By : Foppy
  //###########################################  
  $query = "ALTER TABLE `zk_ishopstore_about` ADD COLUMN `abo_map` TEXT NULL AFTER `abo_contact_us`";
  $db->query($query);




  //###########################################
  // Desc : Add new  shi_price column & change shi_shipment, shi_shipment_detail
  // Create : 2012-01-25
  // Update By : Unzo  
  //###########################################  
  $query = "ALTER TABLE `zk_ishopstore_shipment`     
            ADD COLUMN `shi_price` DOUBLE DEFAULT '0' NULL AFTER `shi_shipment`,    
            CHANGE `shi_shipment` `shi_shipment` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,     
            CHANGE `shi_shipment_detail` `shi_detail` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL ";
  $db->query($query);  




  //###########################################
  // Desc : Create table zk_ishopstore_contactus 
  // Create : 2012-01-25
  // Update By : Foppy
  //###########################################  
  $query = "CREATE TABLE `zk_ishopstore_contactus` (
              `con_id` int(11) NOT NULL auto_increment,
              `con_name` varchar(50) default NULL,
              `con_email` varchar(50) default NULL,
              `con_phone` varchar(50) default NULL,
              `con_headline` varchar(255) default NULL,
              `con_message` text,
              PRIMARY KEY  (`con_id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
  $db->query($query);
 


  //###########################################
  // Desc : Add new  order_obj_status, order_cr_date, order_cr_uid, order_lu_date, order_lu_uid
  // Create : 2012-01-25
  // Update By : Unzo  
  //###########################################  
  $query = "ALTER TABLE `zk_ishopstore_order` ADD COLUMN `order_obj_status` VARCHAR(1) NOT NULL AFTER `order_shipment_id`";
  $db->query($query);    

  $query = "ALTER TABLE `zk_ishopstore_order` ADD COLUMN `order_cr_date` datetime NOT NULL AFTER `order_obj_status`";
  $db->query($query);    

  $query = "ALTER TABLE `zk_ishopstore_order` ADD COLUMN `order_cr_uid` INT(11) NOT NULL AFTER `order_cr_date`";
  $db->query($query);   

  $query = "ALTER TABLE `zk_ishopstore_order` ADD COLUMN `order_lu_date` datetime NOT NULL AFTER `order_cr_uid`";
  $db->query($query); 

  $query = "ALTER TABLE `zk_ishopstore_order` ADD COLUMN `order_lu_uid` INT(11) NOT NULL AFTER `order_lu_date`";
  $db->query($query); 
        


  //###########################################
  // Desc : Add new orditm_obj_status, orditm_cr_date, orditm_cr_uid, orditm_lu_date, orditm_lu_uid
  // Create : 2012-01-25
  // Update By : Unzo  
  //########################################### 
  $query = "ALTER TABLE `zk_ishopstore_order_item` ADD COLUMN `orditm_obj_status` VARCHAR(1) NOT NULL AFTER `orditm_quantity`";
  $db->query($query);    

  $query = "ALTER TABLE `zk_ishopstore_order_item` ADD COLUMN `orditm_cr_date` datetime NOT NULL AFTER `orditm_obj_status`";
  $db->query($query);    

  $query = "ALTER TABLE `zk_ishopstore_order_item` ADD COLUMN `orditm_cr_uid` INT(11) NOT NULL AFTER `orditm_cr_date`";
  $db->query($query);   

  $query = "ALTER TABLE `zk_ishopstore_order_item` ADD COLUMN `orditm_lu_date` datetime NOT NULL AFTER `orditm_cr_uid`";
  $db->query($query); 

  $query = "ALTER TABLE `zk_ishopstore_order_item` ADD COLUMN `orditm_lu_uid` INT(11) NOT NULL AFTER `orditm_lu_date`";
  $db->query($query); 




  //###########################################
  // Desc : Create table zk_ishopstore_contactus 
  // Create : 2012-02-05
  // Update By : Ottowan
  //###########################################  
  $query = "CREATE TABLE `zk_ishopstore_information` (
              `info_uid` int(11) NOT NULL,
              `info_firstname` varchar(255) default NULL,
              `info_lastname` varchar(255) default NULL,
              `info_mobilephone` varchar(50) default NULL,
              `info_gender` int(1) default '1',
              `info_house_no` varchar(50) default NULL,
              `info_moo` varchar(50) default NULL,
              `info_soi` varchar(50) default NULL,
              `info_road` varchar(50) default NULL,
              `info_village` varchar(255) default NULL,
              `info_tambon` varchar(255) default NULL,
              `info_district` varchar(255) default NULL,
              `info_province` varchar(255) default NULL,
              `info_zipcode` int(5) default NULL,
              `info_shop_subdomain` varchar(255) default NULL,
              `info_shop_domain` varchar(255) default NULL,
              `info_shop_name` varchar(255) default NULL,
              `info_shop_database` varchar(255) default NULL,
              `info_shop_description` text,
              `info_shop_icon` text,
              `info_shop_theme` varchar(255) default NULL,
              `information_obj_status` varchar(1) NOT NULL default 'A',
              `information_cr_date` datetime NOT NULL default '1970-01-01 00:00:00',
              `information_cr_uid` int(11) NOT NULL default '0',
              `information_lu_date` datetime NOT NULL default '1970-01-01 00:00:00',
              `information_lu_uid` int(11) NOT NULL default '0',
              PRIMARY KEY  (`info_uid`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
  $db->query($query);   




  //###########################################
  // Desc : Create table zk_ishopstore_inform 
  // Create : 2012-02-05
  // Update By : Foppy
  //########################################### 
  $query = "CREATE TABLE `zk_ishopstore_inform` (
              `inf_id` INT(11) NOT NULL AUTO_INCREMENT,
              `inf_order_id` INT(11) DEFAULT NULL,
              `inf_payment_id` INT(11) DEFAULT NULL,
              `inf_payer_name` VARCHAR(255) DEFAULT NULL,
              `inf_pay` DOUBLE DEFAULT '0',
              `inf_transfer_date` DATETIME DEFAULT NULL,
              `inf_detail` TEXT,
              `inf_status` INT(5) DEFAULT NULL,
              `inf_obj_status` VARCHAR(1) NOT NULL DEFAULT 'A',
              `inf_cr_date` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
              `inf_cr_uid` INT(11) NOT NULL DEFAULT '0',
              `inf_lu_date` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
              `inf_lu_uid` INT(11) NOT NULL DEFAULT '0',
              PRIMARY KEY  (`inf_id`)
            ) ENGINE=MYISAM DEFAULT CHARSET=utf8";
  $db->query($query);     


  //###########################################
  // Desc : ALTER table `zk_ishopstore_payment` 
  // Create : 2012-02-05
  // Update By : Foppy
  //########################################### 
  $query = "  ALTER TABLE `zk_ishopstore_payment`     
              CHANGE `shi_id` `pay_id` INT(11) NOT NULL AUTO_INCREMENT,     
              CHANGE `shi_payment` `pay_payment` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,     
              CHANGE `shi_payment_bank_name` `pay_payment_bank_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,  
              CHANGE `shi_payment_bank_branch` `pay_payment_bank_branch` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,                CHANGE `shi_payment_bank_account_name` `pay_payment_bank_account_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
              CHANGE `shi_payment_bank_account_type` `pay_payment_bank_account_type` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
              CHANGE `shi_payment_bank_account_no` `pay_payment_bank_account_no` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ";
  $db->query($query);     


  //###########################################
  // Desc : DROP table zk_ishopstore_paid
  // Create : 2012-02-05
  // Update By : Foppy
  //########################################### 
  $query = "DROP TABLE `zk_ishopstore_paid`";
  $db->query($query); 

  $query = "ALTER TABLE `zk_ishopstore_about` CHANGE `abo_concact_us` `abo_contact_us` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL ";
  $db->query($query);     


?>